#USED TO TAKE THE HISTORY DATA FROM THE WEATHER STATION AND LOAD INTO AN SQLITE DATABASE
#IT CHECKS FOR IDENTICAL TIME STAMPS AND WON'T PUT COMMON LINES INTO THE DATABASE

#FOrmat:

#a b c d e f g h i j k l m n

#a 20051014081004 	Timestamp
#b 2005-Oct-14 		Date
#c 08:10:04 		Time
#d 27.6 		Indoor temp
#e 10.2 		Outdoor Temp
#f 7.2 			Dew point
#g 35 			Indoor humidity
#h 82 			Outdoor Humidity
#i 0.0 			Windspeed
#j 90.0 		Wind direction
#k E 			Wind direction
#l 10.2 		Windchill
#m 0.00 		Rain 1h
#n 0.00 		Rain 24h

#DATABASE FIELDS:

# DateTime	VARCHAR(14)
# Year		INTEGER
# Month		VARCHAR(3)
# Date		INTEGER
# Hour		INTEGER
# Minute	INTEGER
# Second	INTEGER
# InternalTemp	REAL
# ExternalTemp	REAL
# DewPoint	REAL
# InHumidity	REAL
# OutHumidity	REAL
# Windspeed	REAL
# WindDir	REAL
# windDirStr	VARCHAR(3)
# windChill	REAL
# rain1Hr	REAL
# rain24Hr	REAL


import pylab
from pysqlite2 import dbapi2 as sqlite
import os,sys


class database2300:
	#CONNECTION TO DATABASE
	connection = ""
	#CURSOR USED TO SEND COMMANDS
	cursor = ""
	#VARIABLES USED TO STORE TABLE NAME, DATABASE NAME AND LOG FILE TO IMPORT
	tablename = ""
	database = ""
	logfile = ""
	#VARIABLE TO STORE FIGURE NUMBER
	figure = 0

	x=0.5

	#INITIALISE THE CLASS AND SET THE NAME OF THE TABLE TO CREATE
	#AND THE NAME OF THE DATABASE
	def __init__(self,database = "weather.db",tablename = "hist_data",logfile="log.txt"):
		"""Initialise the database, tablename and logfile"""		
		self.tablename = tablename	
		self.database = database
		self.logfile = logfile

	def readFile(self):
		"""Generater used to read a line of file at a time and return"""
		f = open(self.logfile,"r")		
		for line in f:
			#STRIP OFF THE END OF LINE CHAR
			yield line.strip()
		return

	def writeData(self):
		count = 0
		"""Read in the file and then write each line to the database"""
		w = self.readFile()
		#ONLY ADD THE DATA IF IT DOESN'T ALREADY EXIST	
		for line in w:
			testquery = "SELECT * FROM %s WHERE datetime = '%s'" % (self.tablename,line.split(" ")[0])
			if self.queryDB(testquery) == []:
				self.writeLine(line)
				count = count +1
		print "Records added: %i" % count
	
	#OPEN THE DATABASE, IF IT DOES NOT EXIST THEN CREATE IT AND
	#ADD THE CORRECT FIELDS
	def openDB(self):
		"""If the database of name 'name' does not exist create it"""
		if os.path.isfile(self.database):
			print "Database file exists, I assume it has the correct number of fields"
			self.connection = sqlite.connect(self.database)
			self.cursor = self.connection.cursor() # THIS ALLOWS US TO INTERACT	
		else:
			self.connection = sqlite.connect(self.database)
			self.cursor = self.connection.cursor() # THIS ALLOWS US TO INTERACT
			
			#CREATE THE FIELDS
			#STRING CONTAINING THE FIELDS
			cmd = 'CREATE TABLE %s (datetime VARCHAR(14) PRIMARY KEY, year INTEGER, month VARCHAR(3), date INTEGER, hour INTEGER, minute INTEGER, second INTEGER, inTemp REAL, outTemp REAL, dewPoint REAL, inHum REAL, outHum REAL, windSpeed REAL, windDir REAL, windDirStr VARCHAR(3),windChill REAL, rain1H REAL, rain24H REAL)' % self.tablename
			self.cursor.execute(cmd)
			#COMMIT THE CHANGES
			self.connection.commit()

	def writeLine(self,line):
		"""WRITE A LINE OF DATA TO THE DATABASE"""		
		line =  line.split(" ")	
		date = line[1].split("-")
		time = line[2].split(":")
		#print line, date, time		
			
		#CHECK THE LENGTH IS CORRECT
		if len(line) != 14 and len(date) != 3 and len(time) != 3:
			print "Incorrect Number of points: %i" % len(line.split(" "))
			sys.exit()
		else:
			data = line
			execstring = "INSERT INTO %s VALUES (%s,%i,'%s',%i,%i,%i,%i,%r,%r,%r,%r,%r,%r,%r,'%s',%r,%r,%r)" % (self.tablename,data[0],int(date[0]),date[1],int(date[2]),int(time[0]),int(time[1]),int(time[2]),data[3],data[4],data[5],data[6],data[7],data[8],data[9],data[10],data[11],data[12],data[13])
			self.cursor.execute(execstring)
			self.connection.commit()

	def queryDB(self,query):
		query = query
		#print query
		self.cursor.execute(query)
		return self.cursor.fetchall()

	def plotMonth(self,hour,month,year,field,filename):		
		#PULL OUT THE OUTSIDE TIME
		querystr = "WHERE Hour = %i AND Month = '%s' AND Year = %i" % (hour, month, year)		
		query = "SELECT %s FROM %s %s" % (field,self.tablename,querystr)		
		data = self.queryDB(query)
		date = self.queryDB(u'SELECT %s FROM %s %s' % ("Date,Month","hist_data",querystr))
				
		for i in range(len(date)):
			date[i] = str(date[i][0]) +" "+ date[i][1]
	
		pylab.figure(self.figure)
		p = pylab.plot(data)
		if field == "outTemp":
			titlestr = "Outdoor Temperature"
			labelstr = "Temperature [C]"
		elif field == "inTemp":
			titlestr = "Indoor Temperature"
			labelstr = "Temperature [C]"
		elif field == "DewPoint":
			titlestr = "Dew Point Temperature"
			labelstr = "Temperature [C]"
		elif field == "WindSpeed":
			titlestr = "Wind Speed"
			labelstr = "Windspeed [m/s]"
		elif field == "WindDir":
			titlestr = "Wind Direction"
			labelstr = "Wind Direction [Degrees]"
		elif field == "WindChill":
			titlestr = "Windchill Temperature"
			labelstr = "Temperature [C]"

		#WHY NOT ADD THE AVERAGE TEMPERATURE TO THE PLOT?
		data.sort()
		pylab.axis([0, len(data), data[0][0], data[len(data)-1][0]])

		pylab.text(self.x,data[0][0],"Average: %.2f   Maximum: %.2f   Minimum: %.2f" %(self.avMonth(hour,month,year,field),self.maxMonth(hour,month,year,field),self.minMonth(hour,month,year,field)))

		pylab.title("%s at %i:00 during %s-%i" % (titlestr,hour,month,year))
		pylab.xlabel("Date reading Taken",size=8)
		pylab.ylabel("%s" % labelstr,size=8)

		pylab.xticks(pylab.arange(len(date)),date,rotation=45,size=8)		
		file1 = "MONTH_year_%i_month_%s_hour_%i_%s.png" % (year,month,hour,field)
		figname = os.path.join(filename,file1)	
		pylab.savefig(figname)
		
		self.figure = self.figure +1
		return file1

	def plotDate(self,date, month, year,field,filename):
		"""QUERY THE DATABASE FOR DATA FROM THE ABOVE DATE
		THEN PLOT THIS DATA"""
		condition = "WHERE date = %i AND month = '%s' AND year = %i" % (date,month,year)
		query = "SELECT %s FROM %s %s" % (field, self.tablename,condition)	
		datequery = "SELECT hour FROM %s %s" % (self.tablename, condition)
		
		date1 = date
	
		data = self.queryDB(query)		
		date = self.queryDB(datequery)
		hour = []
		for i in range(len(date)):
			date[i] = str(date[i][0]) 

		pylab.figure(self.figure)
		pylab.plot(data)

		if field == "outTemp":
			titlestr = "Outdoor Temperature"
			labelstr = "Temperature [C]"
		elif field == "inTemp":
			titlestr = "Indoor Temperature"
			labelstr = "Temperature [C]"
		elif field == "DewPoint":
			titlestr = "Dew Point Temperature"
			labelstr = "Temperature [C]"
		elif field == "WindSpeed":
			titlestr = "Wind Speed"
			labelstr = "Windspeed [m/s]"
		elif field == "WindDir":
			titlestr = "Wind Direction"
			labelstr = "Wind Direction [Degrees]"
		elif field == "WindChill":
			titlestr = "Windchill Temperature"
			labelstr = "Temperature [C]"


		#WHY NOT ADD THE AVERAGE TEMPERATURE TO THE PLOT?
		data.sort()
		pylab.axis([0, len(data), data[0][0], data[len(data)-1][0]])
		pylab.text(self.x,data[0][0],"Average: %.2f   Maximum: %.2f   Minimum: %.2f" % (self.avDay(date1,month,year,field),self.maxDay(date1,month,year,field),self.minDay(date1,month,year,field)))
		pylab.title("%s during %i-%s-%i" % (titlestr,int(date1),month,int(year)))
		pylab.xlabel("Time reading Taken",size=8)
		pylab.ylabel("%s" % labelstr,size=8)

		pylab.xticks(pylab.arange(len(date)),date,rotation=0,size=8)		
		file1 = "DATE_year_%i_month_%s_date_%i_%s.png" % (year,month,date1,field)
		figname = os.path.join(filename,file1)
		pylab.savefig(figname)
		self.figure = self.figure +1
		return file1

	def plotYear(self,hour,year,field,filename):
		"""QUERY THE DATABASE FOR DATA FROM THE ABOVE YEAR
		THEN PLOT THE DATA"""
		query = "SELECT %s FROM %s %s" % (field,self.tablename,"WHERE hour = %i AND year = %i"%(hour,year))
		querystr = "WHERE hour = %i AND year = %i" % (hour,year)

		data = self.queryDB(query)		
		date = self.queryDB(u'SELECT %s FROM %s %s' % ("Date,Month","hist_data",querystr))
				
		for i in range(len(date)):
			date[i] = str(date[i][0]) +" "+ date[i][1]
	
		pylab.figure(self.figure)
		p = pylab.plot(data)
		if field == "outTemp":
			titlestr = "Outdoor Temperature"
			labelstr = "Temperature [C]"
		elif field == "inTemp":
			titlestr = "Indoor Temperature"
			labelstr = "Temperature [C]"
		elif field == "DewPoint":
			titlestr = "Dew Point Temperature"
			labelstr = "Temperature [C]"
		elif field == "WindSpeed":
			titlestr = "Wind Speed"
			labelstr = "Windspeed [m/s]"
		elif field == "WindDir":
			titlestr = "Wind Direction"
			labelstr = "Wind Direction [Degrees]"
		elif field == "WindChill":
			titlestr = "Windchill Temperature"
			labelstr = "Temperature [C]"

		#WHY NOT ADD THE AVERAGE TEMPERATURE TO THE PLOT?		
		data.sort()
		pylab.axis([0, len(data), data[0][0], data[len(data)-1][0]])
#		print diff		
#		pylab.text(self.x,data[0][0],"Average: %g" %self.avYear(hour,year,field))
#		pylab.text(self.x, data[0][0]+diff,"Maximum: %g" %self.maxYear(hour,year,field))
#		pylab.text(self.x,data[0][0]+2*diff,"Minimum: %g" %self.minYear(hour,year,field))
		pylab.text(self.x,data[0][0],"Average: %.2f   Maximum: %.2f   Minimum: %.2f" %(self.avYear(hour,year,field),self.maxYear(hour,year,field),self.minYear(hour,year,field)))
		pylab.title("%s at %i:00 during %i" % (titlestr,hour,year))
		pylab.xlabel("Date reading Taken",size=8)
		pylab.ylabel("%s" % labelstr,size=8)

		pylab.xticks(pylab.arange(len(date)),date,rotation=45,size=8)

		file1 = "YEAR_year_%i_hour_%i_%s.png" % (year,hour,field)
		figname = os.path.join(filename,file1)	
		pylab.savefig(figname)
		self.figure = self.figure +1
		return file1

	def avMonth(self,hour,month,year,field):
		condition = "WHERE hour = %i AND month = '%s' AND year = %i" %(hour,month,year)
		query = "SELECT %s FROM %s %s" % (field,self.tablename,condition)
		data = self.queryDB(query)
		average = 0
		if len(data) == 0:
			return -1
		for i in range(len(data)):
			average = average + data[i][0]
		average = average / len(data)
		return average

	def avDay(self,date,month,year,field):
		condition = "WHERE date = %i AND month = '%s' AND year = %i" %(date,month,year)
				
		query = "SELECT %s FROM %s %s" % (field,self.tablename,condition)
		data = self.queryDB(query)
		average = 0
		if len(data) == 0:
			return -1
		for i in range(len(data)):
			average = average + data[i][0]
		average = average / len(data)
		return average

	def avYear(self,hour,year,field):
		condition = "WHERE hour = %i AND year = %i" %(hour,year)
				
		query = "SELECT %s FROM %s %s" % (field,self.tablename,condition)
		data = self.queryDB(query)
		average = 0
		if len(data) == 0:
			return -1
		for i in range(len(data)):
			average = average + data[i][0]
		average = average / len(data)
		return average

	def maxMonth(self,hour,month,year,field):
		condition = "WHERE hour = %i AND month = '%s' AND year = %i" %(hour,month,year)
		query = "SELECT %s FROM %s %s" % (field,self.tablename,condition)
		data = self.queryDB(query)
		average = 0
		if len(data) == 0:
			return -1

		max = pylab.max(data)[0]
		return max

	def maxDay(self,date,month,year,field):
		condition = "WHERE date = %i AND month = '%s' AND year = %i" %(date,month,year)
				
		query = "SELECT %s FROM %s %s" % (field,self.tablename,condition)
		data = self.queryDB(query)
		average = 0
		if len(data) == 0:
			return -1
		max = pylab.max(data)[0]
		return max

	def maxYear(self,hour,year,field):
		condition = "WHERE hour = %i AND year = %i" %(hour,year)
				
		query = "SELECT %s FROM %s %s" % (field,self.tablename,condition)
		data = self.queryDB(query)
		average = 0
		if len(data) == 0:
			return -1
		max = pylab.max(data)[0]
		return max

	def minMonth(self,hour,month,year,field):
		condition = "WHERE hour = %i AND month = '%s' AND year = %i" %(hour,month,year)
		query = "SELECT %s FROM %s %s" % (field,self.tablename,condition)
		data = self.queryDB(query)
		average = 0
		if len(data) == 0:
			return -1

		max = pylab.min(data)[0]
		return max

	def minDay(self,date,month,year,field):
		condition = "WHERE date = %i AND month = '%s' AND year = %i" %(date,month,year)
				
		query = "SELECT %s FROM %s %s" % (field,self.tablename,condition)
		data = self.queryDB(query)
		average = 0
		if len(data) == 0:
			return -1
		max = pylab.min(data)[0]
		return max

	def minYear(self,hour,year,field):
		condition = "WHERE hour = %i AND year = %i" %(hour,year)
				
		query = "SELECT %s FROM %s %s" % (field,self.tablename,condition)
		data = self.queryDB(query)
		average = 0
		if len(data) == 0:
			return -1
		max = pylab.min(data)[0]
		return max

	def searchUnique(self):
		"""Generator used to find all dates in the database
		It searches for years first, then for each year find
		each month. Finally, for each month find all dates.
		Once each date is found yield the result.
		"""		
		query = "SELECT DISTINCT year FROM %s" % self.tablename
		data = self.queryDB(query)
		print data

	def header(self):
		header_str = "<html>\n<head>\n<title>Weather Station Information</title>\n</head>\n<body>\n<h3>Weather station Information</h3>\n<p>\n<h4>Select:</h4><p>\n<table>"
		return header_str

	def footer(self):
		footer_str = "</table>\n</body>\n</html>"
		return footer_str

	def createWeb(self,basepath,indexfile):
		"""Using the generator searchUnique(), a webpage
		is created containing all plots		
		"""
		#OPEN INDEX FILE FOR WRITING
		indfile = open(os.path.join(basepath,indexfile),"w")

		#WRITE HEADER
		indfile.write(self.header())

		#FIND UNIQUE YEAR
		query = "SELECT DISTINCT year FROM %s" % self.tablename
		years = self.queryDB(query)
		for year in years:
			print "Created year: %i" % year[0]
			#CREATE PLOT FOR YEAR
			self.plotYear(00,year[0],"outTemp",basepath)
			#SEARCH FOR MONTHS WITHIN THIS YEAR
			
			#CREATE A STRING TO LINK FOR THE YEAR
			year_file = "year_%i.html" % (year[0])			
			year_str = "<tr>\n<a href = '%s'>%i</a>\n</tr>\n" % (year_file,year[0])
			#WRITE THE STRING TO THE INDEX FILE
			indfile.write(year_str)	
			#CREATE THE YEAR FILE AND WRITE THE HEADER
			yearfile = open(os.path.join(basepath,year_file),"w")
			yearfile.write(self.header())

			query = "SELECT DISTINCT month FROM %s WHERE year = %i" % (self.tablename,year[0])
			months = self.queryDB(query)
			for month in months:
				#STRING FOR ALL OF THE MONTHS
				month_file = "year_%i_month_%s.html" % (year[0],month[0])
				month_str = "<tr>\n<a href = '%s'>%s</a><p>\n</tr>\n" % (month_file,month[0])
				#CREATE THE FILES AND WRITE THE HEADER
				monthfile = open(os.path.join(basepath,month_file),"w")
				monthfile.write(self.header())
				yearfile.write(month_str)
				
				print "\tMonth Created: %s " % month[0]
				#MONTHLY PLOTS:
				fname = self.plotMonth(00,month[0],year[0],"outTemp",basepath)				
				fname1 = self.plotMonth(03,month[0],year[0],"outTemp",basepath)
				fname2 = self.plotMonth(06,month[0],year[0],"outTemp",basepath)
				fname3 = self.plotMonth(9,month[0],year[0],"outTemp",basepath)
				fname4 = self.plotMonth(12,month[0],year[0],"outTemp",basepath)
				fname5 = self.plotMonth(15,month[0],year[0],"outTemp",basepath)
				fname6 = self.plotMonth(18,month[0],year[0],"outTemp",basepath)
				fname7 = self.plotMonth(21,month[0],year[0],"outTemp",basepath)

				#SEARCH FOR DISTINCT DATES AND PLOT THESE
				querydate = "SELECT DISTINCT date FROM %s WHERE year = %i AND month = '%s'" % (self.tablename,year[0],month[0])
				dates = self.queryDB(querydate)				
				for date in dates:
					day_file = "year_%i_month_%s_day_%i.html" %(year[0],month[0],date[0])					
					day_str = "<td>\n<a href = '%s'>%i</a><p>\n</td>\n" %(day_file,date[0])					
					#CREATE THE FILE AND WRITE THE HEADER
					dayfile = open(os.path.join(basepath,day_file),"w")
					dayfile.write(self.header())
					monthfile.write(day_str)

					print "\t\tDate Created: %i" % date[0]
					#CREATE PLOTS FOR EACH DAY OF THE MONTH
					fname = self.plotDate(date[0],month[0],year[0],"outTemp",basepath)
					
					#CREATE LINKS TO IMAGES IN WEBPAGE
					img_inc = "<tr><td><img src='%s' width = '400'></td>"%fname
					dayfile.write(img_inc)
				
			
				monthfile.write(self.footer())
				monthfile.close()
			
			yearfile.write(self.footer())			
			yearfile.close()
		#WRITE THE FOOTER
		indfile.write(self.footer())		
		#CLOSE INDEX FILE
		indfile.close()

if __name__ == "__main__":
	
	a = database2300("weather.db","hist_data","log.txt")
	a.openDB()

	#f1 = open("log.txt","r")
	#data = a.readFile(f1)
	#for line in data:
	#	a.writeLine(line)
	a.writeData()
#	a.plotDate(19,"Feb",2008,"outTemp")
#	a.plotDate(18,"Feb",2008,"outTemp")	
#	a.plotMonth(03,"Feb",2008,"outTemp")	
#	a.plotMonth(00,"Feb",2008,"outTemp")
#	a.plotYear(15,2008,"outTemp","")
	a.createWeb("/media/work_fat32/Python/plotting2300/page/","index.html")
#	pylab.show()
